﻿using DongFangGuoKai.TaskPlatform.Commn;
using DongFangGuoKai.TaskPlatform.Config.Entity;
using DongFangGuoKai.TaskPlatform.Config.Enums;
using Quartz;
using Quartz.Impl;
using Quartz.Impl.AdoJobStore;
using Quartz.Impl.AdoJobStore.Common;
using Quartz.Impl.Matchers;
using Quartz.Impl.Triggers;
using Quartz.Simpl;
using Quartz.Spi;
using Quartz.Util;
using SqlSugar;
using System.Reflection;

namespace DongFangGuoKai.TaskPlatform.Config
{
    /// <summary>
    ///  定时任务调度中心 
    ///  
    /// https://blog.csdn.net/liao0801_123/article/details/83827099  
    /// </summary>
    public class SchedulerCenter
    {
        /// <summary>
        /// 访问数据库
        /// </summary>
        private ISqlSugarClient _Client;

        /// <summary>
        /// 数据链接
        /// </summary>
        private IDbProvider _DbProvider;

        /// <summary>
        /// 调度器（容器）
        /// </summary>
        private IScheduler _Scheduler;

        /// <summary>
        /// Job工厂
        /// </summary>
        private IJobFactory _JobFactory;

        public SchedulerCenter(ISqlSugarClient client, IJobFactory jobFactory, IDbProvider dbProvider)
        {
            _Client = client;
            _JobFactory = jobFactory;

            _DbProvider = dbProvider;


            InitAll().Wait();
        }

        private async Task InitAll()
        {
            //初始化数据库表结构
            InitDBTableAsync().Wait();

            InitSchedulerAsync(_JobFactory).Wait();

            //开启调度器
            if (_Scheduler.InStandbyMode)
            {
                _Scheduler.Start().Wait();
            }

            await Task.CompletedTask;
        }

        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        private async Task InitDBTableAsync()
        {
            var check_sql = @"SELECT
	                                    COUNT (1)
                                    FROM
	                                    sys.tables
                                    WHERE
	                                    name IN (
		                                    'QRTZ_BLOB_TRIGGERS',
		                                    'QRTZ_CALENDARS',
		                                    'QRTZ_CRON_TRIGGERS',
		                                    'QRTZ_FIRED_TRIGGERS',
		                                    'QRTZ_JOB_DETAILS',
		                                    'QRTZ_LOCKS',
		                                    'QRTZ_PAUSED_TRIGGER_GRPS',
		                                    'QRTZ_SCHEDULER_STATE',
		                                    'QRTZ_SIMPLE_TRIGGERS',
		                                    'QRTZ_SIMPROP_TRIGGERS',
		                                    'QRTZ_TRIGGERS'
	                                    );";

            //查询关于QuartZ的表是否都存在，如果有一个不存在
            int count = _Client.Ado.GetInt(check_sql);

            if (count == 0)
            {
                // 获取程序的基目录。 
                string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Config\\tables_sqlServer.sql");
                string init_sql;
                try
                {
                    init_sql = await File.ReadAllTextAsync(path);
                }
                catch
                {
                    #region  Quartz源

                    init_sql = @"
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
                    ALTER TABLE [dbo].[QRTZ_TRIGGERS] DROP CONSTRAINT FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
                    ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] DROP CONSTRAINT FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
                    ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISFOREIGNKEY') = 1)
                    ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] DROP CONSTRAINT FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS
                    ;
                    
                    IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QRTZ_JOB_LISTENERS_QRTZ_JOB_DETAILS]') AND parent_object_id = OBJECT_ID(N'[dbo].[QRTZ_JOB_LISTENERS]'))
                    ALTER TABLE [dbo].[QRTZ_JOB_LISTENERS] DROP CONSTRAINT [FK_QRTZ_JOB_LISTENERS_QRTZ_JOB_DETAILS]
                    
                    IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_QRTZ_TRIGGER_LISTENERS_QRTZ_TRIGGERS]') AND parent_object_id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGER_LISTENERS]'))
                    ALTER TABLE [dbo].[QRTZ_TRIGGER_LISTENERS] DROP CONSTRAINT [FK_QRTZ_TRIGGER_LISTENERS_QRTZ_TRIGGERS]
                    
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CALENDARS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_CALENDARS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_CRON_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_CRON_TRIGGERS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_BLOB_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_BLOB_TRIGGERS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_FIRED_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_FIRED_TRIGGERS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_PAUSED_TRIGGER_GRPS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS]
                    ;
                    
                    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QRTZ_JOB_LISTENERS]') AND type in (N'U'))
                    DROP TABLE [dbo].[QRTZ_JOB_LISTENERS]
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SCHEDULER_STATE]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_SCHEDULER_STATE]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_LOCKS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_LOCKS]
                    ;
                    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGER_LISTENERS]') AND type in (N'U'))
                    DROP TABLE [dbo].[QRTZ_TRIGGER_LISTENERS]
                    
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_JOB_DETAILS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_JOB_DETAILS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPLE_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS]
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_SIMPROP_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].QRTZ_SIMPROP_TRIGGERS
                    ;
                    
                    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QRTZ_TRIGGERS]') AND OBJECTPROPERTY(id, N'ISUSERTABLE') = 1)
                    DROP TABLE [dbo].[QRTZ_TRIGGERS]
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_CALENDARS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [CALENDAR_NAME] [NVARCHAR] (200)  NOT NULL ,
                      [CALENDAR] [VARBINARY](MAX) NOT NULL
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_CRON_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [CRON_EXPRESSION] [NVARCHAR] (120)  NOT NULL ,
                      [TIME_ZONE_ID] [NVARCHAR] (80) 
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_FIRED_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [ENTRY_ID] [NVARCHAR] (140)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [INSTANCE_NAME] [NVARCHAR] (200)  NOT NULL ,
                      [FIRED_TIME] [BIGINT] NOT NULL ,
                      [SCHED_TIME] [BIGINT] NOT NULL ,
                      [PRIORITY] [INTEGER] NOT NULL ,
                      [STATE] [NVARCHAR] (16)  NOT NULL,
                      [JOB_NAME] [NVARCHAR] (150)  NULL ,
                      [JOB_GROUP] [NVARCHAR] (150)  NULL ,
                      [IS_NONCONCURRENT] BIT  NULL ,
                      [REQUESTS_RECOVERY] BIT  NULL 
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL 
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_SCHEDULER_STATE] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [INSTANCE_NAME] [NVARCHAR] (200)  NOT NULL ,
                      [LAST_CHECKIN_TIME] [BIGINT] NOT NULL ,
                      [CHECKIN_INTERVAL] [BIGINT] NOT NULL
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_LOCKS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [LOCK_NAME] [NVARCHAR] (40)  NOT NULL 
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_JOB_DETAILS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [JOB_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [JOB_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [DESCRIPTION] [NVARCHAR] (250) NULL ,
                      [JOB_CLASS_NAME] [NVARCHAR] (250)  NOT NULL ,
                      [IS_DURABLE] BIT  NOT NULL ,
                      [IS_NONCONCURRENT] BIT  NOT NULL ,
                      [IS_UPDATE_DATA] BIT  NOT NULL ,
                      [REQUESTS_RECOVERY] BIT  NOT NULL ,
                      [JOB_DATA] [VARBINARY](MAX) NULL
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [REPEAT_COUNT] [INTEGER] NOT NULL ,
                      [REPEAT_INTERVAL] [BIGINT] NOT NULL ,
                      [TIMES_TRIGGERED] [INTEGER] NOT NULL
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [STR_PROP_1] [NVARCHAR] (512) NULL,
                      [STR_PROP_2] [NVARCHAR] (512) NULL,
                      [STR_PROP_3] [NVARCHAR] (512) NULL,
                      [INT_PROP_1] [INT] NULL,
                      [INT_PROP_2] [INT] NULL,
                      [LONG_PROP_1] [BIGINT] NULL,
                      [LONG_PROP_2] [BIGINT] NULL,
                      [DEC_PROP_1] [NUMERIC] (13,4) NULL,
                      [DEC_PROP_2] [NUMERIC] (13,4) NULL,
                      [BOOL_PROP_1] BIT NULL,
                      [BOOL_PROP_2] BIT NULL,
                      [TIME_ZONE_ID] [NVARCHAR] (80) NULL 
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_BLOB_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [BLOB_DATA] [VARBINARY](MAX) NULL
                    )
                    ;
                    
                    CREATE TABLE [dbo].[QRTZ_TRIGGERS] (
                      [SCHED_NAME] [NVARCHAR] (120)  NOT NULL ,
                      [TRIGGER_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [TRIGGER_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [JOB_NAME] [NVARCHAR] (150)  NOT NULL ,
                      [JOB_GROUP] [NVARCHAR] (150)  NOT NULL ,
                      [DESCRIPTION] [NVARCHAR] (250) NULL ,
                      [NEXT_FIRE_TIME] [BIGINT] NULL ,
                      [PREV_FIRE_TIME] [BIGINT] NULL ,
                      [PRIORITY] [INTEGER] NULL ,
                      [TRIGGER_STATE] [NVARCHAR] (16)  NOT NULL ,
                      [TRIGGER_TYPE] [NVARCHAR] (8)  NOT NULL ,
                      [START_TIME] [BIGINT] NOT NULL ,
                      [END_TIME] [BIGINT] NULL ,
                      [CALENDAR_NAME] [NVARCHAR] (200)  NULL ,
                      [MISFIRE_INSTR] [INTEGER] NULL ,
                      [JOB_DATA] [VARBINARY](MAX) NULL
                    )
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_CALENDARS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_CALENDARS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [CALENDAR_NAME]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_CRON_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_FIRED_TRIGGERS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_FIRED_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [ENTRY_ID]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_PAUSED_TRIGGER_GRPS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_PAUSED_TRIGGER_GRPS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_SCHEDULER_STATE] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_SCHEDULER_STATE] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [INSTANCE_NAME]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_LOCKS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_LOCKS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [LOCK_NAME]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_JOB_DETAILS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_JOB_DETAILS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [JOB_NAME],
                        [JOB_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_SIMPLE_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_SIMPROP_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_TRIGGERS] WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].QRTZ_BLOB_TRIGGERS WITH NOCHECK ADD
                      CONSTRAINT [PK_QRTZ_BLOB_TRIGGERS] PRIMARY KEY  CLUSTERED
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) 
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_CRON_TRIGGERS] ADD
                      CONSTRAINT [FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) ON DELETE CASCADE
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_SIMPLE_TRIGGERS] ADD
                      CONSTRAINT [FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
                      (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) ON DELETE CASCADE
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_SIMPROP_TRIGGERS] ADD
                      CONSTRAINT [FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS] FOREIGN KEY
                      (
                    	[SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) REFERENCES [dbo].[QRTZ_TRIGGERS] (
                        [SCHED_NAME],
                        [TRIGGER_NAME],
                        [TRIGGER_GROUP]
                      ) ON DELETE CASCADE
                    ;
                    
                    ALTER TABLE [dbo].[QRTZ_TRIGGERS] ADD
                      CONSTRAINT [FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS] FOREIGN KEY
                      (
                        [SCHED_NAME],
                        [JOB_NAME],
                        [JOB_GROUP]
                      ) REFERENCES [dbo].[QRTZ_JOB_DETAILS] (
                        [SCHED_NAME],
                        [JOB_NAME],
                        [JOB_GROUP]
                      )
                    ;
                    
                    CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP)
                    CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP)
                    CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME)
                    CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP)
                    CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE)
                    CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE)
                    CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE)
                    CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME)
                    CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME)
                    CREATE INDEX IDX_QRTZ_T_NFT_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME)
                    CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE)
                    CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE)
                    
                    CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME)
                    CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY)
                    CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP)
                    CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP)
                    CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
                    CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP)
                    ;
                    ";


                    #endregion  
                }

                await _Client.Ado.ExecuteCommandAsync(init_sql); //初始化数据库 
            }
            await Task.CompletedTask;
        }


        /// <summary>
        /// 初始化Scheduler
        /// </summary>
        /// <returns></returns>
        private async Task InitSchedulerAsync(IJobFactory jobFactory)
        {
            if (_Scheduler == null)
            {
                DBConnectionManager.Instance.AddConnectionProvider("default", _DbProvider);

                //Nuget: "Quartz.Serialization.Json   
                var serializer = new JsonObjectSerializer();
                serializer.Initialize();

                //配置保存到数据库的相关信息 
                JobStoreTX jobStore = new JobStoreTX()
                {
                    //数据源名称
                    DataSource = "default",
                    //表名前缀  
                    TablePrefix = "QRTZ_",
                    InstanceId = "AUTO",
                    //驱动类型
                    DriverDelegateType = typeof(SqlServerDelegate).AssemblyQualifiedName,
                    ObjectSerializer = serializer,
                    ThreadPoolSize = 20,  //线程池数量    
                    Clustered = true,  //重启
                    //AcquireTriggersWithinLock = true, Job是否加锁  
                };
                DirectSchedulerFactory.Instance.CreateScheduler("bennyScheduler", "AUTO", new DefaultThreadPool() { ThreadCount = 20 }, jobStore);

                //关联数据库得到Scheduler的实例
                _Scheduler = await SchedulerRepository.Instance.Lookup("bennyScheduler");
                _Scheduler.JobFactory = jobFactory;
            }

            await Task.CompletedTask;
        }


        /// <summary>
        /// 获取所有的Job集合
        /// </summary>
        /// <returns></returns>
        public async Task<List<JobInfoEntity>> GetAllJobAsync()
        {
            List<JobKey> jobKeyList = new List<JobKey>();
            List<JobInfoEntity> jobInfoList = new List<JobInfoEntity>();

            IReadOnlyCollection<string> groups = await _Scheduler.GetJobGroupNames();  //获取所有的分组
            foreach (var groupName in groups.OrderBy(t => t))
            {
                jobKeyList.AddRange(await _Scheduler.GetJobKeys(GroupMatcher<JobKey>.GroupContains(groupName)));

                jobInfoList.Add(new JobInfoEntity { GroupName = groupName });
            }

            foreach (var jobKey in jobKeyList.OrderBy(t => t.Name))
            {
                var jobDetail = await _Scheduler.GetJobDetail(jobKey);
                var triggersList = await _Scheduler.GetTriggersOfJob(jobKey);
                var triggers = triggersList.AsEnumerable().FirstOrDefault();


                ///只支持了trigger的两种方式  简单模式, corn表达式模式
                var interval = string.Empty;
                var intervalCode = string.Empty;

                if (triggers is SimpleTriggerImpl)
                {
                    intervalCode = "Simple";
                    interval = (triggers as SimpleTriggerImpl)?.RepeatInterval.ToString();
                }
                else
                {
                    interval = (triggers as CronTriggerImpl)?.CronExpressionString;
                    intervalCode = "Cron";
                }


                foreach (var jobInfo in jobInfoList)
                {
                    if (jobInfo.GroupName == jobKey.Group)
                    {
                        jobInfo.JobInfoList.Add(new JobInfo()
                        {
                            Name = jobKey.Name,
                            TriggerState = await _Scheduler.GetTriggerState(triggers.Key),
                            PreviousFireTime = triggers.GetPreviousFireTimeUtc()?.LocalDateTime,
                            NextFireTime = triggers.GetNextFireTimeUtc()?.LocalDateTime,
                            BeginTime = triggers.StartTimeUtc.LocalDateTime,
                            Interval = interval,
                            IntervalCode = intervalCode,
                            EndTime = triggers.EndTimeUtc?.LocalDateTime,
                            Description = jobDetail.Description
                        });
                        continue;
                    }
                }
            }

            return jobInfoList;

        }

        /// <summary>
        ///获取所有分组名称
        /// </summary>
        /// <returns></returns>
        public async Task<IEnumerable<string>> GetJobGroupNames()
        {
            return await _Scheduler.GetJobGroupNames();
        }

        /// <summary>
        /// 添加一个工作调度
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="runNumber"></param>
        /// <returns></returns>
        public async Task<BaseResult> AddScheduleJobAsync(ScheduleEntity entity)
        {
            var result = new BaseResult();
            try
            {
                //检查任务是否已存在
                var jobKey = new JobKey(entity.JobName, entity.JobGroup);
                if (await _Scheduler.CheckExists(jobKey))
                {
                    result.Success = false;
                    result.Message = "任务已存在";
                    return result;
                }

                IJobConfigurator jobConfigurator;

                Type type;
                {
                    try
                    {
                        string saveFilePath = $"{Directory.GetCurrentDirectory()}\\Extenddll\\{entity.DllName}";
                        Assembly assembly = Assembly.LoadFrom(saveFilePath);
                        type = assembly.GetType(entity.JobInstanceName);
                    }
                    catch (Exception ex)
                    {
                        result.Success = false;
                        result.Message = "程序集或者Job实现类找不到";
                        return result;
                    }

                }

                //Type type = typeof(TestJob);
                jobConfigurator = JobBuilder.Create(type);
                // 定义这个工作，并将其绑定到我们的IJob实现类

                IJobDetail job = jobConfigurator
                    .WithDescription(entity.Description)
                    .WithIdentity(entity.JobName, entity.JobGroup)

                    .Build();


                // 创建时间触发器
                ITrigger trigger;
                //校验是否正确的执行周期表达式
                if (entity.TriggerType == TriggerTypeEnum.Cron)
                {
                    if (CronExpression.IsValidExpression(entity.Cron) == false)
                    {
                        result.Success = false;
                        result.Message = "Cron表达式格式错误";
                        return result;
                    }

                    trigger = CreateCronTrigger(entity);
                }
                else
                {
                    trigger = CreateSimpleTrigger(entity);
                }
                // 告诉Quartz使用我们的触发器来安排作业
                await _Scheduler.ScheduleJob(job, trigger);
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 添加一个工作调度
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="runNumber"></param>
        /// <returns></returns>
        public async Task<BaseResult> AddScheduleToJobAsync(ScheduleEntity entity)
        {
            var result = new BaseResult();
            try
            {
                //检查任务是否已存在
                var jobKey = new JobKey(entity.JobName, entity.JobGroup);
                if (await _Scheduler.CheckExists(jobKey))
                {
                    result.Success = false;
                    result.Message = "任务已存在";
                    return result;
                }

                IJobConfigurator jobConfigurator;

                Type type;
                {
                    try
                    {
                        //获取程序的基目录
                        string path = AppDomain.CurrentDomain.BaseDirectory;
                        path = Path.Combine(path, "DongFangGuoKai.TaskPlatform.Job.dll");
                        Assembly assembly = Assembly.LoadFrom(path);
                        type = assembly.GetType(entity.JobInstanceName);

                    }
                    catch (Exception ex)
                    {
                        result.Success = false;
                        result.Message = "程序集或者Job实现类找不到";
                        return result;
                    }

                }

                {
                    //Type type = typeof(TestJob);
                    jobConfigurator = JobBuilder.Create(type);
                    // 定义这个工作，并将其绑定到我们的IJob实现类  
                }


                //Type type = typeof(TestJob);
                //jobConfigurator = JobBuilder.Create<MyJob2>();
                // 定义这个工作，并将其绑定到我们的IJob实现类

                IJobDetail job = jobConfigurator
                    .WithDescription(entity.Description)
                    .WithIdentity(entity.JobName, entity.JobGroup)
                    .Build();


                // 创建时间触发器
                ITrigger trigger;
                //校验是否正确的执行周期表达式
                if (entity.TriggerType == TriggerTypeEnum.Cron)
                {
                    if (CronExpression.IsValidExpression(entity.Cron) == false)
                    {
                        result.Success = false;
                        result.Message = "Cron表达式格式错误";
                        return result;
                    }

                    trigger = CreateCronTrigger(entity);
                }
                else
                {
                    trigger = CreateSimpleTrigger(entity);
                }
                // 告诉Quartz使用我们的触发器来安排作业
                await _Scheduler.ScheduleJob(job, trigger);
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 开启Schedule容器
        /// </summary>
        /// <returns></returns>
        public async Task StartSchedule()
        {
            //开启调度器
            if (_Scheduler.InStandbyMode)
            {
                _Scheduler.Start().Wait();
            }

            await Task.CompletedTask;
        }

        /// <summary>
        /// 暂停/删除 指定的计划
        /// </summary>
        /// <param name="jobGroup">任务分组</param>
        /// <param name="jobName">任务名称</param>
        /// <param name="isDelete">停止并删除任务</param>
        /// <returns></returns>
        public async Task<BaseResult> StopOrDeleleteScheduleJobAsync(string jobGroup, string jobName, bool isDelete = false)
        {
            BaseResult result;
            try
            {
                await _Scheduler.PauseJob(new JobKey(jobName, jobGroup)); //暂停
                if (isDelete)
                {
                    await _Scheduler.DeleteJob(new JobKey(jobName, jobGroup));
                    result = new BaseResult
                    {
                        Success = true,
                        Message = "删除任务计划成功！"
                    };
                }
                else
                {
                    result = new BaseResult
                    {
                        Success = true,
                        Message = "停止任务计划成功！"
                    };
                }   

            }
            catch (Exception ex)
            {
                result = new BaseResult
                {
                    Success = false,
                    Message = "停止任务计划失败" + ex.Message
                };
            }
            return result;
        }

        /// <summary>
        /// 恢复运行暂停的任务
        /// </summary>
        /// <param name="jobName">任务名称</param>
        /// <param name="jobGroup">任务分组</param>
        public async Task<BaseResult> ResumeJobAsync(string jobGroup, string jobName)
        {
            BaseResult result = new BaseResult();
            try
            {
                //检查任务是否存在
                var jobKey = new JobKey(jobName, jobGroup);
                if (await _Scheduler.CheckExists(jobKey))
                {
                    var jobDetail = await _Scheduler.GetJobDetail(jobKey);
                    var endTime = jobDetail.JobDataMap.GetString("EndAt");
                    if (!string.IsNullOrWhiteSpace(endTime) && DateTime.Parse(endTime) <= DateTime.Now)
                    {
                        result.Success = false;
                        result.Message = "Job的结束时间已过期。";
                    }
                    else
                    {
                        //任务已经存在则暂停任务
                        await _Scheduler.ResumeJob(jobKey);
                        result.Message = "恢复任务计划成功！";
                        result.Success = true;
                    }
                }
                else
                {
                    result.Success = false;
                    result.Message = "任务不存在";
                }
            }
            catch (Exception ex)
            {
                result.Message = "恢复任务计划失败！";
                result.Success = false;

            }
            return result;
        }

        /// <summary>
        /// 创建类型Cron的触发器
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private ITrigger CreateCronTrigger(ScheduleEntity entity)
        {
            // 作业触发器
            return TriggerBuilder.Create()

                   .WithIdentity(entity.JobName, entity.JobGroup)
                   .StartAt(entity.BeginTime)//开始时间
                                             //.EndAt(entity.EndTime)//结束时间
                   .WithCronSchedule(entity.Cron, cronScheduleBuilder => cronScheduleBuilder.WithMisfireHandlingInstructionFireAndProceed())//指定cron表达式
                   .ForJob(entity.JobName, entity.JobGroup)//作业名称
                   .Build();
        }


        /// <summary>
        /// 创建类型Simple的触发器
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        private ITrigger CreateSimpleTrigger(ScheduleEntity entity)
        {
            //作业触发器
            if (entity.RunTimes.HasValue && entity.RunTimes > 0)
            {
                return TriggerBuilder.Create()
               .WithIdentity(entity.JobName, entity.JobGroup)
               .StartAt(entity.BeginTime)//开始时间
                                          .EndAt(entity.EndTime)//结束数据
               .WithSimpleSchedule(x =>
               {
                   x.WithIntervalInSeconds(entity.IntervalSecond.Value)//执行时间间隔，单位秒
                        .WithRepeatCount(entity.RunTimes.Value)//执行次数、默认从0开始
                        .WithMisfireHandlingInstructionFireNow();
               })
               .ForJob(entity.JobName, entity.JobGroup)//作业名称
               .Build();
            }
            else
            {
                return TriggerBuilder.Create()
               .WithIdentity(entity.JobName, entity.JobGroup)
               .StartAt(entity.BeginTime)//开始时间
                                        .EndAt(entity.EndTime)//结束数据
               .WithSimpleSchedule(x =>
               {
                   x.WithIntervalInSeconds(entity.IntervalSecond.Value)//执行时间间隔，单位秒
                        .RepeatForever()//无限循环
                        .WithMisfireHandlingInstructionFireNow();
               })
               .ForJob(entity.JobName, entity.JobGroup)//作业名称
               .Build();
            }

        }

        /// <summary>
        /// 立即执行
        /// </summary>
        /// <param name="jobKey"></param>
        /// <returns></returns>
        public async Task<bool> TriggerJobAsync(JobKey jobKey)
        {
            await _Scheduler.TriggerJob(jobKey);
            return true;
        }

    }
}